package com.gljx.web.controller;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.gljx.constant.ActivitiKey;
import com.gljx.util.*;
import com.gljx.web.Entity.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.gljx.web.service.CustomLogService;
import com.gljx.web.service.DepartmentService;
import com.gljx.web.service.MakeupChargeEntryService;
import com.gljx.web.service.MakeupChargeService;
import com.gljx.web.service.StudentService;
@Controller
@RequestMapping("/")
public class MakeupChargeController {

	//自动注入
	@Autowired
	private MakeupChargeService makeupChargeService;

	//自动注入
	@Autowired
	private MakeupChargeEntryService makeupChargeEntryService;

	//自动注入
	@Autowired
	private DepartmentService departmentService;

	//自动注入
	@Autowired
	private CustomLogService customLogService;

	//自动注入
	@Autowired
	private StudentService studentService;

    //自定义流程工具
    private Axamine axamine = new Axamine();

	/**
	 * 查询补考收费
	 * @param request
	 * @return
	 */
	@RequestMapping("/queryMakeupCharge/look.action")
	public String queryMakeupCharge(HttpServletRequest request,String type){
		PageBean<MakeupCharge> pb = new PageBean<>();
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		//时间格式化
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		//获取当前时间
		String endTime = sdf.format(new Date());
		//获取前三天
		String beginTime = TimeFormat.getThreeDay();
		try {
			//获取分页
			pb = getPageBean(request,user.getFcompanyid(),beginTime,endTime);
			//获取分页数据
			List<MakeupCharge> makeupCharges = pb.getBeanList();
			//获取子类信息
			for (MakeupCharge makeupCharge : makeupCharges) {
				List<MakeupChargeEntry> makeupChargeEntries = makeupChargeEntryService.queryMakeupChargeEntryByFparentId(makeupCharge.getFid(),"","");
				makeupCharge.setMakeupChargeEntrys(makeupChargeEntries);
			}
			//合计信息总条数
			String messageAmount="共有"+pb.getTr()+"条信息";
			//把数据传到页面
			request.setAttribute("messageAmount",messageAmount);
			request.setAttribute("beginTime", beginTime);
			request.setAttribute("endTime", endTime);
			request.setAttribute("makeupCharges", makeupCharges);
			request.setAttribute("pb", pb);
			request.setAttribute("flag", type);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "/backHome/financialManagement/makeupCharge/makeupChargeList";
	}

	/**
	 * 根据条件查询补考收费
	 * @param request
	 * @param studentName 学员
	 * @param cfidcardno 身份证号
	 * @param cfkemu 科目
	 * @param beginTime 开始时间
	 * @param endTime 结束时间
	 * @return
	 */
	@RequestMapping("/queryMakeupChargeByinfo")
	public String queryMakeupChargeByinfo(HttpServletRequest request,String studentName,String cfidcardno,String cfkemu,String beginTime,String endTime){
		//List<String> finds = makeupChargeEntryService.queryMakeupChargeEntryByInfo(studentName, cfidcardno);
		PageBean<MakeupCharge> pb = new PageBean<>();
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		//获取分页
		try {
			//去除空格
			studentName = studentName.trim();
			cfidcardno = cfidcardno.trim();
			cfkemu = cfkemu.trim();
			pb = getPageBean2(request,user.getFcompanyid(),cfkemu,beginTime,endTime,studentName,cfidcardno);
			List<MakeupCharge> makeupCharges = pb.getBeanList();
			for (MakeupCharge makeupCharge : makeupCharges) {
				List<MakeupChargeEntry> makeupChargeEntries = makeupChargeEntryService.queryMakeupChargeEntryByFparentId(makeupCharge.getFid(),studentName,cfidcardno);
				if(makeupChargeEntries == null || makeupChargeEntries.size() == 0){
					
				}else{
					makeupCharge.setMakeupChargeEntrys(makeupChargeEntries);
				}

			}
			//合计信息总条数
			String messageAmount="共有"+pb.getTr()+"条信息";
			//把数据传到页面
			request.setAttribute("messageAmount",messageAmount);
			request.setAttribute("makeupCharges", makeupCharges);
			request.setAttribute("pb", pb);
			request.setAttribute("studentName", studentName);
			request.setAttribute("cfidcardno", cfidcardno);
			request.setAttribute("cfkemu", cfkemu);
			request.setAttribute("beginTime", beginTime);
			request.setAttribute("endTime", endTime);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "/backHome/financialManagement/makeupCharge/makeupChargeList";
	}

	/**
	 * 跳转到添加页面
	 * @param request
	 * @return
	 */
	@RequestMapping("/insertMakeupCharge")
	public String insertMakeupCharge(HttpServletRequest request,String type){
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		//获取部门
		Department department = departmentService.queryAdminByFid(user.getFcompanyid());
		//获取最大编码
		/*String fnumber = customLogService.selectFunmber("ct_ds_makeupcharge",user.getFcompanyid());*/
		//获取下一个编码
		String number = GetFnumber.fnumbers("BK");
		//获取当前时间
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String time = sdf.format(new Date());
		//把数据传到页面
		request.setAttribute("name", user.getName());
		request.setAttribute("time", time);
		request.setAttribute("fnumber", number);
		request.setAttribute("department", department);
		request.setAttribute("type", type);
		return "/backHome/financialManagement/makeupCharge/addMakeupCharge";
	}

	/**
	 * 查询学员
	 * @param name
	 * @param tel
	 * @return
	 */
	@RequestMapping("/queryStuByPsql")
	@ResponseBody
    public List<Student> queryStuByPsql(String name, String tel, String kemu, HttpServletRequest request) {
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        StringBuffer psql = new StringBuffer();
		psql.append("select  to_char(t.CFRXSJ,'YYYY-MM-DD HH24:MI:SS') cfrxsj,to_char(t.CFKMYHERQ,'YYYY-MM-DD HH24:MI:SS')cfkmyherq,");
		psql.append("to_char(t.CFKMEHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmehgrq,to_char(t.CFKMSHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmshgrq,");
		psql.append("to_char(t.CFKMYBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmybjgsj,to_char(t.CFKMSBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmsbjgsj,");
		psql.append("to_char(t.CFKMEBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmebjgsj,t.FID, t.FNUMBER,t.fname_l2,t.CFIDCARDNO,t.CFLINKADDRESS,");
		psql.append("t.CFSTUDENTSTATE,t.CFRECEIVED, t.CFARREARAGE, t.CFFAVORABLE, t.CFKMYKSS,t.CFKMEKSS, t.CFKMSKSS,t.CFSJHM,t.cfslzjcxdh,");
        psql.append("c.fname_l2 cfclazzName,c.cfprice cfamount FROM CT_DS_STUDENT T  left join CT_DS_CLAZZ C ON T.cfclazzid=C.FID where  t.FCONTROLUNITID = '" + user.getFcompanyid() + "' ");
        if("1".equals(kemu)){
			psql.append(" and t.cfstudentstate = '3' and t.cfkmykss > 1 ");
		}else if("2".equals(kemu)){
			psql.append(" and (t.cfstudentstate = '5' or t.cfstudentstate = '6' or t.cfstudentstate = '17' or t.cfstudentstate = '18') and t.cfkmekss > 1 ");
		}else{
			psql.append(" and (t.cfstudentstate = '8' or t.cfstudentstate = '9' or t.cfstudentstate = '15' or t.cfstudentstate = '16' or t.cfstudentstate = '20') and t.cfkmskss > 1 ");
		}
		if("".equals(tel) || tel == null){

		}else{
			psql.append(" and t.CFSJHM = '"+tel+"'");
		}
		if("".equals(name) || name == null){

		}else{
			psql.append(" and t.fname_l2 like '%"+name+"%'");
		}
		List<Student> students = studentService.queryStuBySql(psql.toString());
		return students;
	}

	/**
	 * 添加补考收费
	 * @param request
	 * @param makeupCharge 属性自动封装
	 * @return
	 */
	@RequestMapping("/addMakeupCharge")
	public String addMakeupCharge(HttpServletRequest request,MakeupCharge makeupCharge){
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		//利用UUID生成fid
		makeupCharge.setFid(GetFid.UUIDIntoFid());
		//控制单元id
		makeupCharge.setFcontrolunitid(user.getFcompanyid());
		//创建人id
		makeupCharge.setFcreatorid(user.getFid());
		//公司id
		makeupCharge.setCfficompanyid(user.getFcompanyid());
		//获取当前时间
		String createTime = TimeFormat.timeFormat(new Date());
		//当前时间
		makeupCharge.setFcreatetime(createTime);
		//操作人id
		makeupCharge.setFhandlerid(user.getFid());
		//状态
        makeupCharge.setCfbillstate((long) 3);
        /**获取子类信息集合**/
		//序列
		String[] fseq = request.getParameterValues("fseq");
		//学员编码
		String[] cfstudentno = request.getParameterValues("cfstudentno");
		//学员id
		String[] cfstudentid = request.getParameterValues("cfstudentid");
		//学员姓名
		String[] studentName = request.getParameterValues("studentName");
		//学员身份证号
		String[] cfidcardno = request.getParameterValues("cfidcardno");
		//地址
		String[] cfidcardaddress = request.getParameterValues("cfidcardaddress");
		//申请车型
		String[] cfsqcx = request.getParameterValues("cfsqcx");
		//学员状态
		String[] cfstate = request.getParameterValues("cfstate");
		//考试次数
		String[] cfkscs = request.getParameterValues("cfkscs");
		//缺考次数
		String[] cfqkcs = request.getParameterValues("cfqkcs");
		//应收金额
		String[] cfysje = request.getParameterValues("cfysje");
		//现金
		String[] cfcash = request.getParameterValues("cfcash");
		//刷卡
		String[] cfcard = request.getParameterValues("cfcard");
		//实收金额
		String[] cfsjje = request.getParameterValues("cfsjje");
		//备注
		String[] cfremark = request.getParameterValues("cfremark");
		//添加
		makeupChargeService.addMakeupCharge(makeupCharge,fseq,cfstudentno,cfstudentid,cfidcardno,cfidcardaddress,cfsqcx,cfstate,
				cfkscs,cfqkcs,cfysje,cfcash,cfcard,cfsjje,cfremark);

		//添加日志
		List<CustomLog> customLogs = new ArrayList<>();
		for (int i = 0; i < cfstudentid.length; i++) {
			CustomLog customLog = new CustomLog();
			customLog.setFid(GetFid.UUIDIntoFid());
			customLog.setFcreatetime(createTime);
			customLog.setFcreatorid(user.getFid());
			customLog.setCfstudentid(cfstudentid[i]);
            customLog.setFnameL2("补考收费");
            customLog.setFdescriptionL2("新增学员:" + cfstudentno[i] + studentName[i] + "缴费金额：" + cfsjje[i] + "单据编号:" + makeupCharge.getFnumber());
            //控制单元
			customLog.setFcontrolunitid(user.getFcompanyid());
			customLogs.add(customLog);
		}
		customLogService.insertCustomLog(customLogs);
		//customLogService.addLog("财务管理", createTime+"对补考收费进行添加操作", null, user.getFid());
		//重定向到添加页面
		return "redirect:/DrivingSys/insertMakeupCharge?type=3";
	}

	/**
	 * 根据fid删除
	 * @param request
	 * @param deleteId 可以多个
	 * @param fid 单个
	 * @return
	 */
	@RequestMapping("/deleteMakeupChargeByFid")
    public String deleteMakeupChargeByFid(HttpServletRequest request, String deleteId[], String fid, String[] fnumber, String fnumber2) {
        //获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		String time = TimeFormat.timeFormat(new Date());
        ActitiviUtil actitiviUtil = new ActitiviUtil();
        List<CustomLog> customLogs = new ArrayList<>();
		if (fid == null) {
			for (int i = 0; i < deleteId.length; i++) {
                //删除流程
                actitiviUtil.deleteTask(ActivitiKey.PUBLIC_KEY, fnumber[i]);
                List<MakeupChargeEntry> makeupChargeEntrys = makeupChargeEntryService.queryMakeupChargeEntryByFparentId(deleteId[i], "", "");
				for (MakeupChargeEntry makeupChargeEntry : makeupChargeEntrys) {
					CustomLog customLog2 = new CustomLog();
					customLog2.setFid(GetFid.UUIDIntoFid());
					customLog2.setFcreatetime(time);
					customLog2.setFcreatorid(user.getFid());
					customLog2.setCfstudentid(makeupChargeEntry.getCfstudentid());
                    customLog2.setFnameL2("补考收费");
                    customLog2.setFdescriptionL2("删除学员:" + makeupChargeEntry.getCfstudentno() + makeupChargeEntry.getStudentName());
                    //控制单元
					customLog2.setFcontrolunitid(user.getFcompanyid());
					customLogs.add(customLog2);
				}
			}
		} else {
			List<MakeupChargeEntry> makeupChargeEntrys = makeupChargeEntryService.queryMakeupChargeEntryByFparentId(fid, "", "");
            //删除流程
            actitiviUtil.deleteTask(ActivitiKey.PUBLIC_KEY, fnumber2);
            for (MakeupChargeEntry makeupChargeEntry : makeupChargeEntrys) {
				CustomLog customLog = new CustomLog();
				customLog.setFid(GetFid.UUIDIntoFid());
				customLog.setFcreatetime(time);
				customLog.setFcreatorid(user.getFid());
				customLog.setCfstudentid(makeupChargeEntry.getCfstudentid());
                customLog.setFnameL2("补考收费");
                customLog.setFdescriptionL2("删除学员:" + makeupChargeEntry.getCfstudentno() + makeupChargeEntry.getStudentName());
                //控制单元
				customLog.setFcontrolunitid(user.getFcompanyid());
				customLogs.add(customLog);
			}
		}
		//完成删除
		makeupChargeService.deleteMakeupChargeById(deleteId,fid);
		//添加日志
		customLogService.insertCustomLog(customLogs);
		//customLogService.addLog("财务管理", TimeFormat.timeFormat(new Date())+"对补考收费进行删除操作", null, user.getFid());
		//重定向到查询页面
		return "redirect:/DrivingSys/queryMakeupCharge/look.action";
	}
	
	/**
	 * 根据fid查询
	 * @param request
	 * @param fid 
	 * @return
	 */
	@RequestMapping("/queryMakeupByFid")
	public String queryMakeupByFid(HttpServletRequest request,String fid){
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		//获取部门
		Department department = departmentService.queryAdminByFid(user.getFcompanyid());
		//获取父类信息
		MakeupCharge makeupCharge = makeupChargeService.queryMakeupChargeByFid(fid);
        if (makeupCharge.getCfbillstate() == 5 || makeupCharge.getCfbillstate() == 4) {
            request.setAttribute("sh", "5");
		}
		//获取子类集合
		List<MakeupChargeEntry> makeupChargeEntrys = makeupChargeEntryService.queryMakeupChargeEntryByFparentId(fid, "", "");
        //查询是否是经办人
        Axamine axamine = new Axamine();
        boolean state = axamine.selectAuditPerson(makeupCharge.getFnumber());
        //把数据传到页面
        request.setAttribute("AuditState", state);
        request.setAttribute("department", department);
		request.setAttribute("makeupCharge", makeupCharge);
		request.setAttribute("makeupChargeEntrys", makeupChargeEntrys);
        request.setAttribute("key", ActivitiKey.PUBLIC_KEY);
        return "/backHome/financialManagement/makeupCharge/editMakeupCharge";
    }
	
	/**
	 * 修改
	 * @param request
	 * @param makeupCharge
	 * @return
	 */
	@RequestMapping("/modifyMakeupCharge")
	public String modifyMakeupCharge(HttpServletRequest request,MakeupCharge makeupCharge){
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		//控制单元id
		makeupCharge.setFcontrolunitid(user.getFcompanyid());
		//修改人id
		makeupCharge.setFlastupdateuserid(user.getFid());
		//公司id
		makeupCharge.setCfficompanyid(user.getFcompanyid());
		//获取当前时间
		String updateTime = TimeFormat.timeFormat(new Date());
		//修改时间时间
		makeupCharge.setFlastupdatetime(updateTime);
		//操作人id
		makeupCharge.setFhandlerid(user.getFid());
		/**获取子类信息集合**/
		//序列
		String[] fseq = request.getParameterValues("fseq");
		//学员编码
		String[] cfstudentno = request.getParameterValues("cfstudentno");
		//学员id
		String[] cfstudentid = request.getParameterValues("cfstudentid");
		//学员姓名
		String[] studentName = request.getParameterValues("studentName");
		//学员身份证号
		String[] cfidcardno = request.getParameterValues("cfidcardno");
		//地址
		String[] cfidcardaddress = request.getParameterValues("cfidcardaddress");
		//申请车型
		String[] cfsqcx = request.getParameterValues("cfsqcx");
		//学员状态
		String[] cfstate = request.getParameterValues("cfstate");
		//考试次数
		String[] cfkscs = request.getParameterValues("cfkscs");
		//缺考次数
		String[] cfqkcs = request.getParameterValues("cfqkcs");
		//应收金额
		String[] cfysje = request.getParameterValues("cfysje");
		//现金
		String[] cfcash = request.getParameterValues("cfcash");
		//刷卡
		String[] cfcard = request.getParameterValues("cfcard");
		//实收金额
		String[] cfsjje = request.getParameterValues("cfsjje");
		//备注
		String[] cfremark = request.getParameterValues("cfremark");
		//修改
		makeupChargeService.modifyMakeupCharge(makeupCharge,fseq,cfstudentno,cfstudentid,cfidcardno,cfidcardaddress,cfsqcx,cfstate,
				cfkscs,cfqkcs,cfysje,cfcash,cfcard,cfsjje,cfremark);
		
		//添加日志
		List<CustomLog> customLogs = new ArrayList<>();
		for (int i = 0; i < cfstudentid.length; i++) {
			CustomLog customLog = new CustomLog();
			customLog.setFid(GetFid.UUIDIntoFid());
			customLog.setFcreatetime(updateTime);
			customLog.setFcreatorid(user.getFid());
			customLog.setCfstudentid(cfstudentid[i]);
            customLog.setFnameL2("补考收费");
            customLog.setFdescriptionL2("修改学员:" + cfstudentno[i] + studentName[i] + "缴费金额：" + cfsjje[i] + "单据编号:" + makeupCharge.getFnumber());
            //控制单元
			customLog.setFcontrolunitid(user.getFcompanyid());
			customLogs.add(customLog);
		}
		customLogService.insertCustomLog(customLogs);
		//customLogService.addLog("财务管理", TimeFormat.timeFormat(new Date())+"对补考收费进行删除操作", null, user.getFid());
		//重定向到查询页面
		return "redirect:/DrivingSys/queryMakeupCharge/look.action?type=4";
	}
	
	/**
	 * 根据fid审核
	 * @param fid
	 * @return
	 */
	@RequestMapping("/auditMakeupCharge")
	@ResponseBody
    public int auditMakeupCharge(String fid, HttpServletRequest request, String type, String message, String state, String fnumber,
                                 @RequestParam(value = "studentName[]", defaultValue = "") String[] studentName,
                                 @RequestParam(value = "studentId[]", defaultValue = "") String[] studentId,
                                 @RequestParam(value = "cfstudentno[]", defaultValue = "") String[] cfstudentno) {
        //获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		MakeupCharge makeupCharge = new MakeupCharge();
        if("1".equals(type)){
            ServerResponse serverResponse = axamine.auditProcess(fnumber, state, message);
            if (serverResponse.getStatus() == 1) {
                //结束
                makeupCharge.setCfbillstate(5L);
                makeupCharge.setFid(fid);
                makeupCharge.setFlastupdateuserid(user.getFid());
                makeupCharge.setFauditorid(user.getFid());
				//添加日志
                customLogService.batchInsertState(studentName, studentId, "补考收费", cfstudentno, "单据编号:" + fnumber, "审核");
                return makeupChargeService.auditMakeupCharge(makeupCharge);
            } else if (serverResponse.getStatus() == 2) {
                //推进
                if (!"1".equals(state)) {
                    makeupCharge.setCfbillstate(3L);
                    makeupCharge.setFid(fid);
                    makeupChargeService.auditMakeupCharge(makeupCharge);
                }
                return 2;
            } else {
                //错误
                return 0;
            }
        }else{
            axamine.againstAuditProcess(fnumber, message);
            makeupCharge.setCfbillstate(3L);
            makeupCharge.setFid(fid);
            makeupCharge.setFlastupdateuserid(user.getFid());
            makeupCharge.setFauditorid(user.getFid());
            //添加日志
            customLogService.batchInsertState(studentName, studentId, "补考收费", cfstudentno, "单据编号:" + fnumber, "反审核");
            return makeupChargeService.auditMakeupCharge(makeupCharge);
        }
    }

    /**
     * 根据fid提交
     *
     * @param fid
     * @return
     */
    @RequestMapping("/submitMakeupCharge")
    @ResponseBody
    public int submitMakeupCharge(String fid, HttpServletRequest request, String fnumber, String auditState,
                                  @RequestParam(value = "studentName[]", defaultValue = "") String[] studentName,
                                  @RequestParam(value = "studentId[]", defaultValue = "") String[] studentId,
                                  @RequestParam(value = "cfstudentno[]", defaultValue = "") String[] cfstudentno) {
        MakeupCharge makeupCharge = new MakeupCharge();
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        makeupCharge.setFlastupdateuserid(user.getFid());
        makeupCharge.setFid(fid);
        makeupCharge.setCfbillstate(4L);
        //创建流程
        axamine.submissionProcess(fnumber);
        int i = makeupChargeService.auditMakeupCharge(makeupCharge);
		//添加日志
        customLogService.batchInsertState(studentName, studentId, "补考收费", cfstudentno, "单据编号:" + fnumber, "提交");
        return i;
    }

    @SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类 
	 * @throws Exception
	 */
	public PageBean<MakeupCharge> getPageBean2(HttpServletRequest request,String companyId,String cfkemu,String beginTime,String endTime,String studentName,String cfidcardno) throws Exception
	{

		int offset;//从第几条数据开始加载
		if(request.getParameter("pager.offset")==null){  
			offset=0;  
		}else{  
			offset=Integer.parseInt(request.getParameter("pager.offset"));  
		}	
		//拼接SQL语句
		StringBuffer psql = new  StringBuffer();
		psql.append("select distinct t.fid,t.fnumber,to_char(t.fbizdate,'yyyy-MM-dd')fbizdate,t.cfbillstate,t.cfkemu ");
		psql.append(" from ct_ds_makeupcharge t left join ct_ds_makeupchargeentry m on t.fid = m.fparentid left join ct_ds_student s ");
		psql.append(" on m.cfstudentid = s.fid where t.FCONTROLUNITID= '"+companyId+"'");
		if("".equals(cfkemu)){

		}else{
			psql.append(" and t.cfkemu = "+cfkemu+"");
		}
		if("".equals(studentName)){

		}else{
			psql.append(" and s.fname_l2 like '%"+studentName+"%'");
		}
		if("".equals(cfidcardno)){

		}else{
			psql.append("  and m.cfidcardno like '%"+cfidcardno+"%'");
		}
		if("".equals(beginTime) || "".equals(endTime)){

		}else{
			psql.append("  and t.fbizdate between to_date('"+beginTime+"','YYYY/MM/DD') and to_date('"+endTime+"','YYYY/MM/DD')");
		}
		psql.append(" order by to_char(t.fbizdate,'yyyy-MM-dd') desc");
		int psize = 100; //每页显示数
        int pindex=offset/psize;//+1  页码从0开始
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap= makeupChargeService.selectPage(pindex, psql.toString(), psize);
		PageBean<MakeupCharge> pb=new PageBean<>();
		pb.setPc(pindex); //页码
		Map<String, Object> paramMapCount=getPageBeanCount(psql.toString()); //取记录总数
		pb.setTr((int)paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<MakeupCharge>)paramMap.get("v_cur")); //每页数据集
		return pb;
	}

	@SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类 
	 * @throws Exception
	 */
	public PageBean<MakeupCharge> getPageBean(HttpServletRequest request,String companyId,String beginTime,String endTime) throws Exception
	{

		int offset;//从第几条数据开始加载
		if(request.getParameter("pager.offset")==null){  
			offset=0;  
		}else{  
			offset=Integer.parseInt(request.getParameter("pager.offset"));  
		}	
		//拼接SQL语句
		StringBuffer psql = new  StringBuffer();
		psql.append("select t.fid,t.fnumber,to_char(t.fbizdate,'yyyy-MM-dd')fbizdate,t.cfbillstate,t.cfkemu ");
		psql.append("from ct_ds_makeupcharge t where t.FCONTROLUNITID= '"+companyId+"'");
		psql.append(" and t.fbizdate between to_date('"+beginTime+"','YYYY/MM/DD') and to_date('"+endTime+"','YYYY/MM/DD')");
		psql.append(" order by to_char(t.fbizdate,'yyyy-MM-dd') desc");
		int psize = 10; //每页显示数
        int pindex=offset/psize;//+1  页码从0开始
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap= makeupChargeService.selectPage(pindex, psql.toString(), psize);
		PageBean<MakeupCharge> pb=new PageBean<>();
		pb.setPc(pindex); //页码
		Map<String, Object> paramMapCount=getPageBeanCount(psql.toString()); //取记录总数
		pb.setTr((int)paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<MakeupCharge>)paramMap.get("v_cur")); //每页数据集
		return pb;
	}

	/**
	 * 取记录总数 dmz 20160921
	 * @param psql  SQL语句
	 * @return Map
	 * @throws Exception
	 */
	public Map<String, Object> getPageBeanCount(String psql) throws Exception
	{

		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap=makeupChargeService.selectPageCount(psql);
		return paramMap;		
	}

	/**
	 * 导出Execl
	 *
	 * @param request
	 * @param response
	 * @param studentName
	 * @param cfidcardno
	 * @param cfkemu
	 * @param beginTime
	 * @param endTime
	 */
	@RequestMapping("/importMakeupCharge")
	public void importMakeupCharge(HttpServletRequest request, HttpServletResponse response, String studentName,
								   String cfidcardno, String cfkemu, String beginTime, String endTime) {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		//定义接收类型
		List<Object> data = new ArrayList<>();
		//查询数据
        List<MakeupChargeEntry> makeupChargeEntrys = makeupChargeEntryService.selectMakeupChargeByInfo(user.getFcompanyid(), studentName.trim(), cfidcardno.trim(), cfkemu, beginTime, endTime);
        for (MakeupChargeEntry makeupChargeEntry : makeupChargeEntrys) {
			data.add(makeupChargeEntry.getFnumber());
			data.add(makeupChargeEntry.getFbizdate());
			data.add(stateToString.billsState(makeupChargeEntry.getCfbillstate()));
			data.add(makeupChargeEntry.getStudentName());
			data.add(makeupChargeEntry.getCfstudentno());
			data.add(makeupChargeEntry.getCfidcardno());
			data.add(makeupChargeEntry.getCfidcardaddress());
			data.add(makeupChargeEntry.getCfsqcx());
			data.add(stateToString.studentState(makeupChargeEntry.getCfstate()));
			data.add(makeupChargeEntry.getCfysje());
			data.add(makeupChargeEntry.getCfcash());
			data.add(makeupChargeEntry.getCfcard());
			data.add(makeupChargeEntry.getCfsjje());
			data.add(makeupChargeEntry.getCfremark());
			data.add(stateToString.subject(makeupChargeEntry.getCfkemu()));
			data.add(makeupChargeEntry.getCfkscs());
			data.add(makeupChargeEntry.getCfqkcs());
		}
		//头部数据
		String head[] = {"单据编码", "业务日期", "单据状态", "学员", "学员编码", "身份证号", "身份证地址", "申请车型", "学员状态", "应收金额", "现金", "刷卡", "实收金额", "摘要", "科目", "考试次数", "缺考次数"};
		//长度
		Integer length[] = {6500, 3000, 2600, 3000, 6500, 5500, 7000, 3000, 5000, 4000, 2600, 2600, 2600, 4000, 2600, 2600, 2600};


		List<Object> data2= listForSum(data,head,9,4);
		try {
			//导出Excel
			CommonExport.importExcelSum(response, "补考收费" + TimeFormat.getNowTime2() + ".xls", "补考收费", data, head, length,data2);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 *统计方法
	 * @param list1 完成遍历的集合
	 * @param head  表头
	 * @param num   求和的第一个字段的下标
	 * @param sum  需要增加多少个字段
	 * @return
	 */
	public List<Object> listForSum(List<Object> list1,String[] head,int num,int sum){

		Double money=0.0;

		List<Object>list=new ArrayList<>();
		DecimalFormat df = new DecimalFormat("#.00");
		list.add("合计：");
		for (int i = 0; i < num - 1; i++) {
			list.add(" ");
		}
		for(int i=0;i<sum;i++){

			for (int j = 0; j < list1.size() / head.length; j++) {
				if (list1.get(num + head.length*j)==null || "".equals(list1.get(num + head.length * j))) {
					money += 0.00;
				} else {
					money += Double.parseDouble(list1.get(num + head.length * j) + "");
				}
			}
			if(money==0.00||money==null){
				list.add("0.00");
			}else{
				if(".00".equals(df.format(money))){
					list.add("");
				}else{
					list.add(df.format(money));
				}
			}
			num++;
			money = 0.00;
		}
		return list;
	}

}
